/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.model;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.db.SqlManager;
import com.exception.DataException;
import com.javabeans.Aula;
import com.javabeans.Carrera;
import com.javabeans.DiaSemana;
import com.javabeans.Grupo;
import com.javabeans.GrupoHorario;
import com.javabeans.Materia;
import com.javabeans.MateriaPensum;
import com.javabeans.PeriodoDia;
import com.javabeans.Usuario;

/**
 * @author Administrador
 */
public class AdminGrupo implements GrupoManager{
    private Connection c;

    public AdminGrupo(Connection c){
        this.c = c;
    }
    public int getIdGrupo(int idGestion) throws DataException{
        int res = 0;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT IFNULL(max(IDGRUPO)+1,1) AS ID FROM GRUPO WHERE IDGESTION="+idGestion;
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            rs.next();
            res = rs.getInt("ID");
        }catch(SQLException e){
            throw new DataException(" AdminGrupo -> getIdGrupo(int idGestion) "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }

    @Override
    public void saveGrupo(Grupo g)throws DataException {
        PreparedStatement preSt = null;
        try{
            String query = "INSERT INTO GRUPO VALUES("+g.getIdGestion()+","+g.getIdGrupo();
            query += ", "+g.getNroGrupo()+","+g.isLimitado();
            query += ", "+g.getCupo()+","+g.getInscritos();
            query += ", '"+g.getMotivoBaja()+"',"+g.isActivo()+","+g.getIdDocente()+")";            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminGrupo -> saveGrupo(Grupo g) "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    @Override
    public void saveGrupos(List<Grupo> grupos) throws DataException{
        PreparedStatement preSt = null;
        int i = 0;
        String query = "INSERT INTO GRUPO VALUES";
        try{
            for(Grupo g:grupos){
                query += "("+g.getIdGestion()+","+g.getIdGrupo();
                query += ", "+g.getNroGrupo()+","+g.isLimitado();
                query += ", "+g.getCupo()+","+g.getInscritos();
                query += ", '"+g.getMotivoBaja()+"',"+g.isActivo()+","+g.getIdDocente()+"),";
                i++;
            }
            if(i > 0){
                i = query.lastIndexOf(",");
                query = query.substring(0, i);
            }            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException e){
            throw new DataException(" AdminGrupo -> saveGrupos(List<Grupo> grupos) sql = "+e.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    @Override
    public void updateGrupo(Grupo g)throws DataException{
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE GRUPO SET NRO_GRUPO = "+g.getNroGrupo();
            query+= ", LIMITADO = "+g.isLimitado();
            query+= ", CUPO = "+g.getCupo()+" , IDDOCENTE="+g.getIdDocente();
            query+= " WHERE IDGESTION="+g.getIdGestion()+" AND IDGRUPO="+g.getIdGrupo();            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminGrupo -> updateGrupo(Grupo g) "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    @Override
    public void deleteGrupo(int idGestion, int idGrupo)throws DataException{
        PreparedStatement preSt = null;
        try{
            String query = "DELETE FROM GRUPO_MATERIA WHERE IDGESTION="+idGestion+" AND IDGRUPO="+idGrupo;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
            
            query = "DELETE FROM GRUPO_HORARIO WHERE IDGESTION="+idGestion+" AND IDGRUPO="+idGrupo;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
            
            query = "DELETE FROM GRUPO WHERE IDGRUPO="+idGrupo;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminGrupo -> deleteGrupo(int idGestion,int idGrupo) "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public boolean isUseForDelete(int idGestion, int idGrupo) throws DataException{
        boolean res = false;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        // verificamos antes de eliminar si existe un alumno inscrito
        String query = "SELECT IDGRUPO";
        query += " FROM INSCRITO";
        query += " WHERE IDGESTION="+idGestion+" AND IDGRUPO="+idGrupo+" LIMIT 1";
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next()){
                res = true;
            }
        }catch(SQLException e){
            throw new DataException(" AdminGrupo -> isUseForDelete(int idGestion, int idGrupo) es = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }
    
    @Override
    public void darBajaGrupo(Grupo g)throws DataException{
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE GRUPO SET MOTIVOBAJA = '"+g.getMotivoBaja();
            query+= "', ACTIVO = "+g.isActivo();
            query+= " WHERE IDGESTION="+g.getIdGestion()+" AND IDGRUPO="+g.getIdGrupo();            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminGrupo -> darBajaGrupo(Grupo g) "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    @Override
    public void darAltaGrupo(Grupo g)throws DataException{
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE GRUPO SET MOTIVOBAJA = '"+g.getMotivoBaja();
            query+= "', ACTIVO = "+g.isActivo();
            query+= " WHERE IDGESTION="+g.getIdGestion()+" AND IDGRUPO="+g.getIdGrupo();            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException(" AdminGrupo -> darAltaGrupo(Grupo g) "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    
    public List<Grupo> getGruposAllPorCarr(int idGestion,int idCarrera, boolean activo) throws DataException{
        List<Grupo> grupos = new ArrayList<Grupo>();
        ResultSet rs = null;
        ResultSet rs2 = null;
        PreparedStatement preSt = null;
        String query = "";
        try{
            query = "SELECT G.IDGRUPO";
            query += ", G.NRO_GRUPO";
            query += ", G.LIMITADO";
            query += ", G.CUPO";
            query += ", G.INSCRITOS";
            query += ", G.MOTIVOBAJA";
            query += ", G.IDDOCENTE";
            query += ", CONCAT(D.NOMBRE,' ',D.APELLIDO) NOM_DOCENTE";
            query += ", M.NOMBRE";
            query += ", GM.IDMATERIA";
            query += " FROM GRUPO G";
            query += " INNER JOIN GESTION GE ON G.IDGESTION=GE.IDGESTION AND GE.IDGESTION="+idGestion;
            query += " INNER JOIN USUARIO D ON G.IDDOCENTE=D.IDUSUARIO";
            query += " INNER JOIN (GRUPO_MATERIA GM INNER JOIN MATERIA M ON GM.IDCARRERA=M.IDCARRERA AND GM.IDMATERIA=M.IDMATERIA AND GM.IDCARRERA="+idCarrera+")";
            query += "  ON G.IDGESTION=GM.IDGESTION AND G.IDGRUPO=GM.IDGRUPO";
            query += " WHERE G.ACTIVO="+activo;
            query += " ORDER BY M.NOMBRE, G.NRO_GRUPO";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Grupo g = new Grupo();
                g.setIdGestion(idGestion);
                g.setIdGrupo(rs.getInt("G.IDGRUPO"));
                g.setNroGrupo(rs.getInt("G.NRO_GRUPO"));
                g.setLimitado(rs.getBoolean("G.LIMITADO"));
                g.setCupo(rs.getInt("G.CUPO"));
                g.setInscritos(rs.getInt("G.INSCRITOS"));
                g.setMotivoBaja(rs.getString("G.MOTIVOBAJA"));
                g.setIdDocente(rs.getInt("G.IDDOCENTE"));
                g.setAux(rs.getInt("IDMATERIA"));
                Usuario docente = new Usuario();
                docente.setNombre(rs.getString("NOM_DOCENTE"));
                g.setDocente(docente);
                
                List<MateriaPensum> materiasP = new ArrayList<MateriaPensum>();
                List<GrupoHorario> ghorarios = new ArrayList<GrupoHorario>();
                query = "SELECT GM.IDPENSUM, GM.IDCARRERA";
                query += ", GM.IDMATERIA";
                query += ", MP.LEVEL, MP.ELECTIVA";
                query += ", C.NOMBRE NOM_CARRERA";
                query += ", C.SIGLA SIGLA_CARRERA";
                query += ", M.NOMBRE NOM_MATERIA";
                query += ", M.SIGLA SIGLA_MATERIA";
                query += " FROM GRUPO_MATERIA GM";
                query += " INNER JOIN MATERIA_PENSUM MP ON GM.IDPENSUM=MP.IDPENSUM AND GM.IDCARRERA=MP.IDCARRERA AND GM.IDMATERIA=MP.IDMATERIA";
                query += " INNER JOIN CARRERA C ON GM.IDCARRERA=C.IDCARRERA";
                query += " INNER JOIN MATERIA M ON GM.IDCARRERA=M.IDCARRERA AND GM.IDMATERIA=M.IDMATERIA";
                query += " WHERE GM.IDGESTION="+idGestion+" AND GM.IDGRUPO="+g.getIdGrupo();
                query += " ORDER BY NOM_MATERIA";                
                preSt = c.prepareStatement(query);
                rs2 = preSt.executeQuery();
                while(rs2.next()){
                    MateriaPensum mp = new MateriaPensum();
                    mp.setIdPensum(rs2.getInt("GM.IDPENSUM"));
                    mp.setIdCarrera(rs2.getInt("GM.IDCARRERA"));
                    mp.setIdMateria(rs2.getInt("GM.IDMATERIA"));
                    mp.setLevel(rs2.getInt("MP.LEVEL"));
                    mp.setElectiva(rs2.getBoolean("MP.ELECTIVA"));
                    Materia m = new Materia();
                    m.setNombre(rs2.getString("NOM_MATERIA"));
                    m.setSigla(rs2.getString("SIGLA_MATERIA"));
                    Carrera ca = new Carrera();
                    ca.setNombre(rs2.getString("NOM_CARRERA"));
                    ca.setSigla(rs2.getString("SIGLA_CARRERA"));
                    m.setCarrera(ca);
                    mp.setMateria(m);
                    materiasP.add(mp);
                }
                g.setMateriasPensum(materiasP);
                // adicionamos horarios
                query = "SELECT H.IDFACULTAD, H.IDPERIODO_DIA";
                query += ", H.IDDIA_SEMANA";
                query += ", DATE_FORMAT(PD.HORAINI,'%H:%i') HORAINI";
                query += ", DATE_FORMAT(PD.HORAFIN,'%H:%i') HORAFIN";
                query += ", DS.NOMBRE";
                query += ", H.IDAULA, A.NRO, A.DESCRIPCION";
                query += " FROM GRUPO_HORARIO H";
                query += " INNER JOIN PERIODO_DIA PD ON H.IDFACULTAD=PD.IDFACULTAD AND H.IDPERIODO_DIA=PD.IDPERIODO_DIA";
                query += " INNER JOIN DIA_SEMANA DS ON H.IDDIA_SEMANA=DS.IDDIA_SEMANA";
                query += " INNER JOIN AULA A ON H.IDAULA=A.IDAULA";
                query += " WHERE H.IDGESTION="+idGestion+" AND H.IDGRUPO="+g.getIdGrupo();
                query += " ORDER BY DS.ORDEN";                
                preSt = c.prepareStatement(query);
                rs2 = preSt.executeQuery();
                while(rs2.next()){
                    GrupoHorario gh = new GrupoHorario();
                    gh.setIdGestion(idGestion);
                    gh.setIdGrupo(g.getIdGrupo());
                    gh.setIdFacultad(rs2.getInt("H.IDFACULTAD"));
                    gh.setIdPeriodoDia(rs2.getInt("H.IDPERIODO_DIA"));
                    gh.setIdDiaSemana(rs2.getInt("H.IDDIA_SEMANA"));
                    gh.setIdAula(rs2.getInt("H.IDAULA"));
                    
                    DiaSemana ds = new DiaSemana();
                    ds.setNombre(rs2.getString("DS.NOMBRE"));
                    PeriodoDia pd = new PeriodoDia();
                    pd.setHoraIni(rs2.getString("HORAINI"));
                    pd.setHoraFin(rs2.getString("HORAFIN"));
                    Aula a = new Aula();
                    a.setNro(rs2.getString("A.NRO"));
                    a.setDescripcion(rs2.getString("A.DESCRIPCION"));
                    
                    gh.setPeriodoDia(pd);
                    gh.setDiaSemana(ds);
                    gh.setAula(a);
                    ghorarios.add(gh);
                }
                g.setGrupoHorarios(ghorarios);
                grupos.add(g);
            }
        }catch(SQLException e){
            throw new DataException(" AdminGrupo -> getGruposAllPorCarr(int idGestion,int idCarrera) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closeRs(rs2);
            SqlManager.closePreSt(preSt);
        }
        return grupos;
    }
    
    public Grupo getGrupo(int idGestion,int idGrupo) throws DataException{
        Grupo g = new Grupo();
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "";
        try{
            query = "SELECT G.IDGRUPO";
            query += ", G.NRO_GRUPO";
            query += ", G.LIMITADO";
            query += ", G.CUPO";
            query += ", G.INSCRITOS";
            query += ", G.IDDOCENTE";
            query += ", CONCAT(D.NOMBRE,' ',D.APELLIDO) NOM_DOCENTE";
            query += " FROM GRUPO G";
            query += " INNER JOIN USUARIO D ON G.IDDOCENTE=D.IDUSUARIO";
            query += " WHERE G.IDGESTION="+idGestion+" AND G.IDGRUPO="+idGrupo;            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            if(rs.next()){
                g.setIdGestion(idGestion);
                g.setIdGrupo(idGrupo);
                g.setNroGrupo(rs.getInt("G.NRO_GRUPO"));
                g.setLimitado(rs.getBoolean("G.LIMITADO"));
                g.setCupo(rs.getInt("G.CUPO"));
                g.setInscritos(rs.getInt("G.INSCRITOS"));
                g.setIdDocente(rs.getInt("G.IDDOCENTE"));
                Usuario docente = new Usuario();
                docente.setNombre(rs.getString("NOM_DOCENTE"));
                g.setDocente(docente);
                
                List<MateriaPensum> materiasP = new ArrayList<MateriaPensum>();
                List<GrupoHorario> ghorarios = new ArrayList<GrupoHorario>();
                query = "SELECT GM.IDPENSUM, GM.IDCARRERA";
                query += ", GM.IDMATERIA";
                query += ", MP.LEVEL, MP.ELECTIVA";
                query += ", C.NOMBRE NOM_CARRERA";
                query += ", C.SIGLA SIGLA_CARRERA";
                query += ", M.NOMBRE NOM_MATERIA";
                query += ", M.SIGLA SIGLA_MATERIA";
                query += " FROM GRUPO_MATERIA GM";
                query += " INNER JOIN MATERIA_PENSUM MP ON GM.IDPENSUM=MP.IDPENSUM AND GM.IDCARRERA=MP.IDCARRERA AND GM.IDMATERIA=MP.IDMATERIA";
                query += " INNER JOIN CARRERA C ON GM.IDCARRERA=C.IDCARRERA";
                query += " INNER JOIN MATERIA M ON GM.IDCARRERA=M.IDCARRERA AND GM.IDMATERIA=M.IDMATERIA";
                query += " WHERE GM.IDGESTION="+idGestion+" AND GM.IDGRUPO="+idGrupo;
                query += " ORDER BY NOM_MATERIA";                
                preSt = c.prepareStatement(query);
                rs = preSt.executeQuery();
                while(rs.next()){
                    MateriaPensum mp = new MateriaPensum();
                    mp.setIdPensum(rs.getInt("GM.IDPENSUM"));
                    mp.setIdCarrera(rs.getInt("GM.IDCARRERA"));
                    mp.setIdMateria(rs.getInt("GM.IDMATERIA"));
                    mp.setLevel(rs.getInt("MP.LEVEL"));
                    mp.setElectiva(rs.getBoolean("MP.ELECTIVA"));
                    Materia m = new Materia();
                    m.setNombre(rs.getString("NOM_MATERIA"));
                    m.setSigla(rs.getString("SIGLA_MATERIA"));
                    Carrera ca = new Carrera();
                    ca.setNombre(rs.getString("NOM_CARRERA"));
                    ca.setSigla(rs.getString("SIGLA_CARRERA"));
                    m.setCarrera(ca);
                    mp.setMateria(m);
                    materiasP.add(mp);
                }
                g.setMateriasPensum(materiasP);
                // adicionamos horarios
                query = "SELECT H.IDFACULTAD, H.IDPERIODO_DIA";
                query += ", H.IDDIA_SEMANA";
                query += ", DATE_FORMAT(PD.HORAINI,'%H:%i') HORAINI";
                query += ", DATE_FORMAT(PD.HORAFIN,'%H:%i') HORAFIN";
                query += ", DS.NOMBRE";
                query += ", H.IDAULA , A.NRO, A.DESCRIPCION";
                query += " FROM GRUPO_HORARIO H";
                query += " INNER JOIN PERIODO_DIA PD ON H.IDFACULTAD=PD.IDFACULTAD AND H.IDPERIODO_DIA=PD.IDPERIODO_DIA";
                query += " INNER JOIN DIA_SEMANA DS ON H.IDDIA_SEMANA=DS.IDDIA_SEMANA";
                query += " INNER JOIN AULA A ON H.IDAULA=A.IDAULA";
                query += " WHERE H.IDGESTION="+idGestion+" AND H.IDGRUPO="+idGrupo;
                query += " ORDER BY DS.ORDEN";                
                preSt = c.prepareStatement(query);
                rs = preSt.executeQuery();
                while(rs.next()){
                    GrupoHorario gh = new GrupoHorario();
                    gh.setIdGestion(idGestion);
                    gh.setIdGrupo(g.getIdGrupo());
                    gh.setIdFacultad(rs.getInt("H.IDFACULTAD"));
                    gh.setIdPeriodoDia(rs.getInt("H.IDPERIODO_DIA"));
                    gh.setIdDiaSemana(rs.getInt("H.IDDIA_SEMANA"));
                    gh.setIdAula(rs.getInt("H.IDAULA"));
                    
                    DiaSemana ds = new DiaSemana();
                    ds.setNombre(rs.getString("DS.NOMBRE"));
                    PeriodoDia pd = new PeriodoDia();
                    pd.setHoraIni(rs.getString("HORAINI"));
                    pd.setHoraFin(rs.getString("HORAFIN"));
                    Aula a = new Aula();
                    a.setNro(rs.getString("A.NRO"));
                    a.setDescripcion(rs.getString("A.DESCRIPCION"));
                    
                    gh.setPeriodoDia(pd);
                    gh.setDiaSemana(ds);
                    gh.setAula(a);
                    ghorarios.add(gh);
                }
                g.setGrupoHorarios(ghorarios);
            }
        }catch(SQLException e){
            throw new DataException(" AdminGrupo -> getGrupo(int idGestion,int idGrupo) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return g;
    }
    
    @Override
    public int getNroGrupo(int idGestion,int idCarrera, int idMateria) throws DataException{
        int res = 0;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT IFNULL(MAX(G.NRO_GRUPO)+1,1) AS NRO_GRUPO";
        query += " FROM GRUPO G";
        query += " INNER JOIN (GRUPO_MATERIA GM INNER JOIN PENSUM P ON GM.IDPENSUM=P.IDPENSUM)";
        query += " ON G.IDGESTION=GM.IDGESTION AND G.IDGRUPO=GM.IDGRUPO AND GM.IDGESTION="+idGestion+" AND GM.IDCARRERA="+idCarrera+" AND GM.IDMATERIA="+idMateria;        
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            rs.next();
            res = rs.getInt("NRO_GRUPO");
        }catch(SQLException e){
            throw new DataException(" AdminGrupo -> getNroGrupo(int idGestion,int idCarrera, int idMateria) "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }    
    
    public List<Grupo> getGrupoXmateriaYdocente(int idDocente) throws DataException {//, int idFacultad
        List<Grupo> grupos = new ArrayList<Grupo>();
        
        
        ResultSet rs = null;
        PreparedStatement preSt = null;
        
        try{
            String query = "SELECT GR.IDGESTION";
            query +=", GR.IDGRUPO";
            query +=", GR.NRO_GRUPO";
            query +=", GR.LIMITADO";
            query +=", GR.CUPO";
            query +=", GR.INSCRITOS";
            query +=", GR.MOTIVOBAJA";
            query +=", GR.IDDOCENTE";
            query +=", (SELECT IDCARRERA FROM CARRERA WHERE IDCARRERA=M.IDCARRERA) IDCARRERA";
            query +=", (SELECT NOMBRE FROM CARRERA WHERE IDCARRERA=M.IDCARRERA) NOM_CARRERA";
            query +=", (SELECT SIGLA FROM CARRERA WHERE IDCARRERA=M.IDCARRERA) SIGLA";
            query +=", M.NOMBRE";
            query +=", GM.IDMATERIA";
            query +=", GM.IDPENSUM";
            query +=", GE.IDGESTION";
            query +=" FROM GRUPO GR";
            query +=" INNER JOIN GESTION GE ON GR.IDGESTION=GE.IDGESTION AND GE.ESTADO=1";
            query +=" INNER JOIN USUARIO D ON GR.IDDOCENTE=D.IDUSUARIO";
            query +=" INNER JOIN (GRUPO_MATERIA GM INNER JOIN MATERIA M ON GM.IDCARRERA=M.IDCARRERA AND GM.IDMATERIA=M.IDMATERIA)";
            query +=" ON GR.IDGESTION=GM.IDGESTION AND GR.IDGRUPO=GM.IDGRUPO";
            query +=" WHERE GR.ACTIVO=1 AND GR.IDDOCENTE="+idDocente;
            query +=" ORDER BY M.NOMBRE, GR.NRO_GRUPO";            
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            while(rs.next()){
                Carrera ca = new Carrera();
                ca.setIdCarrera(rs.getInt("IDCARRERA"));
                ca.setNombre(rs.getString("NOM_CARRERA"));
                ca.setSigla(rs.getString("SIGLA"));
                ca.setAux(rs.getString("GE.IDGESTION"));
                
                Materia m = new Materia();
                m.setIdMateria(rs.getInt("GM.IDMATERIA"));
                m.setNombre(rs.getString("M.NOMBRE"));
                m.setCarrera(ca);
                
                MateriaPensum mp = new MateriaPensum();
                mp.setIdPensum(rs.getInt("GM.IDPENSUM"));
                mp.setMateria(m);
                List<MateriaPensum> matPensum = new ArrayList<MateriaPensum>();
                matPensum.add(mp);
                
                Grupo gru = new Grupo();
                gru.setIdGestion(rs.getInt("GR.IDGESTION"));
                gru.setIdGrupo(rs.getInt("GR.IDGRUPO"));
                gru.setNroGrupo(rs.getInt("GR.NRO_GRUPO"));
                gru.setMateriasPensum(matPensum);

                grupos.add(gru);                
            }
        }catch(SQLException e){
            throw new DataException(" AdminGrupo -> getGrupoXmateriaYdocente(int idDocente) e = "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        
        return grupos;
    } 
    @Override
    public int existeAlumnos(int idGestion, int idGrupo, int idMateria, int idCarrera) throws DataException{
        int res = 0;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT count(I.IDGESTION) CANT\n" +
                       " FROM INSCRITO I\n" +
                       " INNER JOIN ESTUDIANTE E ON I.IDESTUDIANTE=E.IDESTUDIANTE\n" +
                       " INNER JOIN CARRERA C ON I.IDCARRERA=C.IDCARRERA\n" +
                       " WHERE I.IDGESTION="+idGestion+" AND I.IDGRUPO="+idGrupo+" AND I.IDMATERIA="+idMateria+" AND I.IDCARRERA="+idCarrera;        
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            rs.next();
            res = rs.getInt("CANT");
        }catch(SQLException e){
            throw new DataException(" AdminGrupo -> existeAlumnos(idGestion, idGrupo, idMateria, idCarrera) "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }
}
